-- création de la procédure _P_DERECURSIVE_TREE 
CREATE OR ALTER PROCEDURE dbo._P_DERECURSIVE_TREE @TABLE_SCHEMA sysname, 
                                        @TABLE_NAME   sysname, 
                                        @COL_ID       sysname, 
                                        @COL_ID_PERE  sysname, 
                                        @COL_BG       sysname, 
                                        @COL_BD       sysname,
                                        @COL_NIVO     sysname  
AS 
/****************************************************************************** 
* PROCÉDURE DE CALCUL D'UN ARBRE INTERVALLAIRE ACTUELLEMENT EN AUTO RÉFÉRENCE * 
******************************************************************************* 
* Frédéric Brouard   -   SQLpro   -   http://www.sqlspot.com   -   2004-06-10 * 
******************************************************************************* 
* PARAMÈTRES :                                                                * 
*    @TABLE_SCHEMA schéma SQL de la table (en principe dbo)                   * 
*    @TABLE_NAME   nom de la table                                            * 
*    @COL_ID       nom de la colonne colonne clef primaire                    * 
*    @COL_ID_PERE  nom de la col. clef étrangère en auto réf. à la clef prim. * 
*    @COL_BG       nom de la colonne borne gauche                             * 
*    @COL_BD       nom de la colonne borne droite                             * 
******************************************************************************/ 

SET NOCOUNT ON;

-- variables locales 
DECLARE @SQL NVARCHAR(max),@COMPTEUR BIGINT,@MAX_CPTR BIGINT, @POINTEUR BIGINT;

-- on créé la table ##_TMP_TREE_SQLPRO pour stocker temporairement 
-- les clefs et les valeurs en autoréférence de la table à traiter 
   CREATE TABLE ##_TMP_TREE_SQLPRO 
   (CLEF     BIGINT NOT NULL, 
    CLEF_REF BIGINT); 

 -- on créé la table ##_TMP_STACK_SQLPRO pour gérer la pile de dérécursivation
   CREATE TABLE ##_TMP_STACK_SQLPRO 
   (PILE     BIGINT NOT NULL, 
    CLEF     BIGINT NOT NULL PRIMARY KEY, 
    GAUCHE   BIGINT, 
    DROITE   BIGINT,
    DT       DATETIME2 DEFAULT SYSDATETIME()); 
   CREATE INDEX X_CC2 ON ##_TMP_STACK_SQLPRO (PILE) WITH (FILLFACTOR = 80);
--END;

-- on y insère les valeurs dedans 
SET @SQL = 'INSERT INTO ##_TMP_TREE_SQLPRO SELECT ' + @COL_ID + ', ' 
         + @COL_ID_PERE + ' FROM ' + @TABLE_SCHEMA + '.'  + @TABLE_NAME 
EXECUTE (@SQL) 
 
-- initialisation 
SELECT @COMPTEUR = 2, @MAX_CPTR = 2 * COUNT_BIG(*), @POINTEUR = 1
FROM ##_TMP_TREE_SQLPRO; 
 
-- insertion de la racine de l'arbre dans la pile 
INSERT INTO ##_TMP_STACK_SQLPRO (PILE, CLEF, GAUCHE, DROITE)
SELECT 1, CLEF, 1, @MAX_CPTR 
  FROM ##_TMP_TREE_SQLPRO 
 WHERE CLEF_REF IS NULL; 
 
-- et on supprime la référence à la racine 
DELETE FROM ##_TMP_TREE_SQLPRO 
 WHERE CLEF_REF IS NULL; 
 
-- tant que l'on a pas traité toute l'enveloppe intervallaire 
WHILE @COMPTEUR <= (@MAX_CPTR - 2) 
BEGIN 
 
-- s'il existe des lignes à traiter 
   IF EXISTS (SELECT * 
              FROM   ##_TMP_STACK_SQLPRO AS S 
                     INNER JOIN ##_TMP_TREE_SQLPRO AS T 
                           ON S.CLEF = T.CLEF_REF 
              WHERE  S.PILE = @POINTEUR) 
 
   BEGIN  
 
-- empile tant que la ligne analysée a des fils (calcul de la borne gauche) 
       INSERT INTO ##_TMP_STACK_SQLPRO (PILE, CLEF, GAUCHE, DROITE)
       SELECT @POINTEUR + 1, MIN(T.CLEF), @COMPTEUR, NULL 
       FROM   ##_TMP_STACK_SQLPRO AS S 
              INNER JOIN ##_TMP_TREE_SQLPRO AS T 
                    ON S.CLEF = T.CLEF_REF 
       WHERE  S.PILE = @POINTEUR; 
-- supprime la ligne analysée 
       DELETE FROM ##_TMP_TREE_SQLPRO 
       WHERE CLEF = (SELECT CLEF 
                     FROM   ##_TMP_STACK_SQLPRO 
                     WHERE  PILE = @POINTEUR + 1); 
-- incrémente compteur et pointeur 
        SET @COMPTEUR = @COMPTEUR + 1; 
        SET @POINTEUR = @POINTEUR + 1; 
 
     END 
     ELSE 
     BEGIN  
 
-- dépile parce que que la ligne analysée n'a plus de fils 
        UPDATE ##_TMP_STACK_SQLPRO 
        SET    DROITE = @COMPTEUR, 
               PILE = - PILE -- pops the Stack 
        WHERE  PILE = @POINTEUR 
 
-- incrémente lecompteur et décrémente le pointeur 
       SELECT @COMPTEUR = @COMPTEUR + 1, @POINTEUR = @POINTEUR - 1; 
 
     END; 
END; 

-- met à jour la table cible avec ces calculs 
SET @SQL = ' UPDATE ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + 
           ' SET    ' + @COL_BG + ' = S.GAUCHE, ' + 
                        @COL_BD + ' = COALESCE(S.DROITE, ' + CAST(@MAX_CPTR-1 AS VARCHAR(32)) +'), '
                      + @COL_NIVO + ' = ABS(PILE) ' +
           ' FROM   ##_TMP_STACK_SQLPRO S INNER JOIN ' + @TABLE_NAME + ' T ON S.CLEF = T.'+@COL_ID 
EXEC (@SQL) 

-- suppression des tables pseudo temporaires 
IF OBJECT_ID('tempdb..##_TMP_TREE_SQLPRO') IS NOT NULL 
   EXEC ('DROP TABLE ##_TMP_TREE_SQLPRO' ) 

IF OBJECT_ID('tempdb..##_TMP_STACK_SQLPRO') IS NOT NULL 
   EXEC ('DROP TABLE ##_TMP_STACK_SQLPRO') 

GO